﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using CrmDocman.DataCore;

/// <summary>
/// Summary description for SanPhamHelper
/// </summary>
public class SanPhamHelper : TWSData
{
	public SanPhamHelper()
	{
        m_dao = DataBridge.GetInstance();
	}

    public SanPhamDataSet LoadSanPham()
    {

        SanPhamDataSet spDs = new SanPhamDataSet();

        m_dao.FillDataTable(spDs.SanPham);
        m_dao.Connection.CloseConnection();
        return spDs;
    }

    public SanPhamDataSet LoadSanPham(int sanphamID)
    {

        SanPhamDataSet spDs = new SanPhamDataSet();
        string sql = m_dao.BuildSelectQuery(spDs.SanPham);
        sql += " where SanPhamID = " + sanphamID;

        m_dao.FillDataTable(spDs.SanPham, sql);
        m_dao.Connection.CloseConnection();
        return spDs;
    }

    public SanPhamDataSet LoadSanPhamTheoLoai(int loaisanphamID)
    {
        SanPhamDataSet spDs = new SanPhamDataSet();
        string sql = m_dao.BuildSelectQuery(spDs.SanPham);
        sql += " where LoaiSanPham = " + loaisanphamID;

        m_dao.FillDataTable(spDs.SanPham, sql);
        m_dao.Connection.CloseConnection();
        return spDs;
    }

    public bool SaveSanPham(SanPhamDataSet spDs)
    {
        m_dao.UpdateDataTable(spDs.SanPham);
        m_dao.Connection.CloseConnection();
        return true;

    }

    public SanPhamDataSet LoadSanPhamMoi()
    {

        SanPhamDataSet spDs = new SanPhamDataSet();
        string sql = "select top 5 * from dbo.SanPham where SanPhamMoi = 'true' order by ThuTu";

        m_dao.FillDataTable(spDs.SanPham, sql);
        m_dao.Connection.CloseConnection();
        return spDs;
    }

    public SanPhamDataSet LoadSanPhamTheoLoaiCon(int page, int pageSize, string sortColumn, string sortOrder, int type)
    {

        SanPhamDataSet spDs = new SanPhamDataSet();
        string sql = "DECLARE @PageNum AS INT; ";
        sql += "DECLARE @PageSize AS INT; ";
        sql += "SET @PageNum = " + page + "; ";
        sql += "SET @PageSize = " + pageSize + "; ";
        sql += "WITH OrdersRN AS ( ";
        sql += "SELECT ROW_NUMBER() OVER(ORDER BY " + sortColumn + " " + sortOrder + ") AS RowNum, * ";
        sql += "FROM dbo.SanPham where LoaiSanPham = '" + type + "' ) ";
        sql += "select * from OrdersRN ";
        sql += " WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize ";
        sql += " order by " + sortColumn + " " + sortOrder;

        m_dao.FillDataTable(spDs.SanPham, sql);
        m_dao.Connection.CloseConnection();
        return spDs;
    }

    public SanPhamDataSet LoadSanPhamTheoLoaiCon(int page, int pageSize, string sortColumn, string sortOrder, int type,string name)
    {

        SanPhamDataSet spDs = new SanPhamDataSet();
        string sql = "DECLARE @PageNum AS INT; ";
        sql += "DECLARE @PageSize AS INT; ";
        sql += "SET @PageNum = " + page + "; ";
        sql += "SET @PageSize = " + pageSize + "; ";
        sql += "WITH OrdersRN AS ( ";
        sql += "SELECT ROW_NUMBER() OVER(ORDER BY " + sortColumn + " " + sortOrder + ") AS RowNum, * ";
        sql += "FROM dbo.SanPham where LoaiSanPham = '" + type + "' and TenSanPham like '%"+name+"%' ) ";
        sql += "select * from OrdersRN ";
        sql += " WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize ";
        sql += " order by " + sortColumn + " " + sortOrder;

        m_dao.FillDataTable(spDs.SanPham, sql);
        m_dao.Connection.CloseConnection();
        return spDs;
    }

    public int TongSanPhamTheoLoaiCon(int loaisanphamID)
    {
        SanPhamDataSet spDs = new SanPhamDataSet();
        string sql = m_dao.BuildSelectQuery(spDs.SanPham);
        sql += " where LoaiSanPham = " + loaisanphamID;

        m_dao.FillDataTable(spDs.SanPham, sql);
        m_dao.Connection.CloseConnection();
        return spDs.SanPham.Rows.Count;
    }

    public SanPhamDataSet LoadSanPhamTheoLoaiCha(int page, int pageSize, string sortColumn, string sortOrder, int type)
    {

        SanPhamDataSet spDs = new SanPhamDataSet();
        string sql = "DECLARE @PageNum AS INT; ";
        sql += "DECLARE @PageSize AS INT; ";
        sql += "SET @PageNum = " + page + "; ";
        sql += "SET @PageSize = " + pageSize + "; ";
        sql += "WITH OrdersRN AS ( ";
        sql += "SELECT ROW_NUMBER() OVER(ORDER BY " + sortColumn + " " + sortOrder + ") AS RowNum, * ";
        sql += "FROM dbo.SanPham where LoaiSanPham in ( select LoaiSanPhamID from dbo.LoaiSanPham where LoaiSanPhamChaID = '" + type + "' )) ";
        sql += "select * from OrdersRN ";
        sql += " WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize ";
        sql += " order by " + sortColumn + " " + sortOrder;

        m_dao.FillDataTable(spDs.SanPham, sql);
        m_dao.Connection.CloseConnection();
        return spDs;
    }

    public SanPhamDataSet LoadSanPhamPaging(int page, int pageSize, string sortColumn, string sortOrder)
    {

        SanPhamDataSet spDs = new SanPhamDataSet();
        string sql = "DECLARE @PageNum AS INT; ";
        sql += "DECLARE @PageSize AS INT; ";
        sql += "SET @PageNum = " + page + "; ";
        sql += "SET @PageSize = " + pageSize + "; ";
        sql += "WITH OrdersRN AS ( ";
        sql += "SELECT ROW_NUMBER() OVER(ORDER BY " + sortColumn + " " + sortOrder + ") AS RowNum, * ";
        sql += "FROM dbo.SanPham) ";
        sql += "select * from OrdersRN ";
        sql += " WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize ";
        sql += " order by " + sortColumn + " " + sortOrder;

        m_dao.FillDataTable(spDs.SanPham, sql);
        m_dao.Connection.CloseConnection();
        return spDs;
    }

    public SanPhamDataSet LoadSanPhamPaging(int page, int pageSize, string sortColumn, string sortOrder,string name)
    {

        SanPhamDataSet spDs = new SanPhamDataSet();
        string sql = "DECLARE @PageNum AS INT; ";
        sql += "DECLARE @PageSize AS INT; ";
        sql += "SET @PageNum = " + page + "; ";
        sql += "SET @PageSize = " + pageSize + "; ";
        sql += "WITH OrdersRN AS ( ";
        sql += "SELECT ROW_NUMBER() OVER(ORDER BY " + sortColumn + " " + sortOrder + ") AS RowNum, * ";
        sql += "FROM dbo.SanPham where TenSanPham like '%" + name + "%') ";
        sql += "select * from OrdersRN ";
        sql += " WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize ";
        sql += " order by " + sortColumn + " " + sortOrder;

        m_dao.FillDataTable(spDs.SanPham, sql);
        m_dao.Connection.CloseConnection();
        return spDs;
    }

    public int TongSanPhamTheoLoaiCha(int loaisanphamID)
    {
        SanPhamDataSet spDs = new SanPhamDataSet();
        string sql = m_dao.BuildSelectQuery(spDs.SanPham);
        sql += " where LoaiSanPham in ( select LoaiSanPhamID from dbo.LoaiSanPham where LoaiSanPhamChaID = " + loaisanphamID+")";

        m_dao.FillDataTable(spDs.SanPham, sql);
        m_dao.Connection.CloseConnection();
        return spDs.SanPham.Rows.Count;
    }

    public SanPhamDataSet LoadSanPhamTheoLoai(int page, int pageSize, string sortColumn, string sortOrder, int type)
    {
        if (type==0)
        {
            return LoadSanPhamPaging(page, pageSize, sortColumn, sortOrder);
        }
        if (new LoaiSanPhamHelper().IsLoaiSanPhamCha(type))
        {
            return LoadSanPhamTheoLoaiCha( page,  pageSize,  sortColumn,  sortOrder,  type);
        }
        return LoadSanPhamTheoLoaiCon( page,  pageSize,  sortColumn,  sortOrder,  type);
    }

    public SanPhamDataSet LoadSanPhamTheoLoai(int page, int pageSize, string sortColumn, string sortOrder, int type,string name)
    {
        if (type == 0)
        {
            return LoadSanPhamPaging(page, pageSize, sortColumn, sortOrder,name);
        }
        if (new LoaiSanPhamHelper().IsLoaiSanPhamCha(type))
        {
            return LoadSanPhamTheoLoaiCha(page, pageSize, sortColumn, sortOrder, type);
        }
        return LoadSanPhamTheoLoaiCon(page, pageSize, sortColumn, sortOrder, type,name);
    }

    public int TongSanPhamTheoLoai(int loaisanphamID)
    {
        if (loaisanphamID == 0)
        {
            return LoadSanPham().SanPham.Rows.Count;
        }
        if (new LoaiSanPhamHelper().IsLoaiSanPhamCha(loaisanphamID))
        {
            return TongSanPhamTheoLoaiCha(loaisanphamID);
        }
        return TongSanPhamTheoLoaiCon(loaisanphamID);
    }


}